from flask import Flask, render_template, request, jsonify, redirect, url_for, session, flash
from models import db, User, Order, Tag, UserTag, UserActivity, OrderItem, Product, Admin
from datetime import datetime, timedelta
from routes.user_segments import bp as user_segments_bp
import os
import io
import base64
import numpy as np
from sklearn.linear_model import LinearRegression
from scipy import stats
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from werkzeug.utils import secure_filename
from sqlalchemy import func
import pandas as pd
from functools import wraps
import random
import matplotlib.pyplot as plt

plt.style.use('seaborn')
plt.rcParams['font.family'] = 'SimHei'
# 添加文件上传配置
UPLOAD_FOLDER = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads')
ALLOWED_EXTENSIONS = {'csv'}

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:nmdzz25041@localhost/user_profile_system'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['SECRET_KEY'] = 'your-secret-key-here'  # 用于session加密
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(hours=24)  # session过期时间

# 确保上传文件夹存在
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

# 初始化数据库
db.init_app(app)

# 注册蓝图
app.register_blueprint(user_segments_bp)

# 登录验证装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        user = Admin.query.filter_by(username=username).first()
        
        if user and user.check_password(password):
            session['user_id'] = user.id
            session['username'] = user.username
            user.last_login = datetime.now()
            db.session.commit()
            return redirect(url_for('index'))
        else:
            return render_template('login.html', error='用户名或密码错误')
    
    return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form.get('username')
        email = request.form.get('email')
        password = request.form.get('password')
        confirm_password = request.form.get('confirm_password')
        
        if password != confirm_password:
            return render_template('register.html', error='两次输入的密码不一致')
        
        if Admin.query.filter_by(username=username).first():
            return render_template('register.html', error='用户名已存在')
        
        if Admin.query.filter_by(email=email).first():
            return render_template('register.html', error='邮箱已被注册')
        
        user = Admin(username=username, email=email)
        user.set_password(password)
        
        try:
            db.session.add(user)
            db.session.commit()
            return redirect(url_for('login'))
        except Exception as e:
            db.session.rollback()
            return render_template('register.html', error='注册失败，请稍后重试')
    
    return render_template('register.html')

@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))

# 为需要登录的路由添加装饰器
@app.route('/')
@app.route('/index')
@login_required
def index():
    return render_template('index.html')

@app.route('/user-management')
@login_required
def user_management():
    return render_template('user_management.html')

@app.route('/user-tags')
@login_required
def user_tags():
    return render_template('user_tags.html')

@app.route('/user-profile/<int:user_id>')
def user_profile(user_id):
    # 使用join加载用户标签数据
    user = User.query.options(
        db.joinedload(User.tags).joinedload(UserTag.tag)
    ).get_or_404(user_id)
    
    # 获取用户订单
    orders = Order.query.filter_by(user_id=user_id).order_by(Order.order_date.desc()).all()
    
    # 计算统计数据
    total_amount = sum(order.total_amount for order in orders) if orders else 0
    average_amount = total_amount / len(orders) if orders else 0
    last_order_date = orders[0].order_date if orders else None
    
    # 获取消费趋势数据
    # 使用最后一个订单的日期作为结束日期，往前推90天
    if last_order_date:
        end_date = last_order_date
        start_date = end_date - timedelta(days=90)
        
        trend_orders = Order.query.filter(
            Order.user_id == user_id,
            Order.order_date >= start_date,
            Order.order_date <= end_date,
            Order.status == 'completed'
        ).order_by(Order.order_date).all()
        
        # 准备趋势图数据
        dates = []
        amounts = []
        current_date = start_date
        daily_amounts = {}
        
        # 初始化每天的金额为0
        while current_date <= end_date:
            daily_amounts[current_date.date()] = 0
            current_date += timedelta(days=1)
        
        # 累计每天的订单金额
        for order in trend_orders:
            order_date = order.order_date.date()
            if order_date in daily_amounts:  # 只累计在范围内的日期
                daily_amounts[order_date] += order.total_amount
        
        # 转换为列表
        dates = list(daily_amounts.keys())
        amounts = list(daily_amounts.values())
        
        # 准备趋势数据
        consumption_trend = {
            'dates': [date.strftime('%Y-%m-%d') for date in dates],
            'amounts': amounts
        }
    else:
        # 如果没有订单，返回空数据
        consumption_trend = {
            'dates': [],
            'amounts': []
        }
    
    return render_template('user_profile.html',
                         user=user,
                         orders=orders,
                         total_amount=total_amount,
                         average_amount=average_amount,
                         last_order_date=last_order_date,
                         consumption_trend=consumption_trend)

@app.route('/user-segments')
@login_required
def user_segments_page():
    return render_template('user_segments.html')

# API Routes
@app.route('/api/user-stats')
def get_user_stats():
    total_users = User.query.count()
    active_users = sum(1 for user in User.query.all() if user.is_active)
    vip_users = User.query.filter(User.membership_level.in_(['金卡会员', '钻石会员'])).count()
    churn_users = sum(1 for user in User.query.all() if user.is_churn)
    
    return jsonify({
        'total_users': total_users,
        'active_users': active_users,
        'vip_users': vip_users,
        'churn_users': churn_users
    })

@app.route('/api/users')
def get_users():
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)
    search = request.args.get('search', '')
    membership = request.args.get('membership', '')
    status = request.args.get('status', '')
    
    query = User.query
    
    if search:
        query = query.filter(
            (User.username.like(f'%{search}%')) |
            (User.email.like(f'%{search}%')) |
            (User.phone.like(f'%{search}%'))
        )
    
    if membership:
        query = query.filter_by(membership_level=membership)
    
    if status:
        query = query.filter_by(status=(status == 'active'))
    
    pagination = query.paginate(page=page, per_page=per_page)
    users = pagination.items
    
    return jsonify({
        'users': [{
            'id': user.id,
            'username': user.username,
            'email': user.email,
            'phone': user.phone,
            'membership_level': user.membership_level,
            'status': user.status,
            'consumption_score': user.consumption_score
        } for user in users],
        'total': pagination.total,
        'pages': pagination.pages,
        'current_page': page
    })

@app.route('/api/tags', methods=['GET'])
def get_tags():
    tags = Tag.query.all()
    return jsonify([{
        'id': tag.id,
        'name': tag.name,
        'category': tag.category
    } for tag in tags])

@app.route('/api/users/<int:user_id>/tags', methods=['GET', 'POST', 'DELETE'])
def user_tags_api(user_id):
    user = User.query.get_or_404(user_id)
    
    if request.method == 'GET':
        return jsonify([{
            'id': tag.id,
            'name': tag.name,
            'category': tag.category
        } for tag in user.tags])
    
    elif request.method == 'POST':
        tag_ids = request.json.get('tag_ids', [])
        tags = Tag.query.filter(Tag.id.in_(tag_ids)).all()
        user.tags.extend(tags)
        db.session.commit()
        return jsonify({'message': 'Tags added successfully'})
    
    elif request.method == 'DELETE':
        tag_ids = request.json.get('tag_ids', [])
        tags = Tag.query.filter(Tag.id.in_(tag_ids)).all()
        for tag in tags:
            user.tags.remove(tag)
        db.session.commit()
        return jsonify({'message': 'Tags removed successfully'})

@app.route('/api/tags', methods=['POST'])
def create_tag():
    data = request.json
    tag = Tag(
        name=data['name'],
        category=data['category'],
        description=data.get('description', '')
    )
    db.session.add(tag)
    
    try:
        db.session.commit()
        return jsonify({
            'id': tag.id,
            'name': tag.name,
            'category': tag.category,
            'description': tag.description
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 400

@app.route('/api/users/<int:user_id>/consumption-trend')
def user_consumption_trend(user_id):
    end_date = datetime.now()
    start_date = end_date - timedelta(days=30)
    
    orders = Order.query.filter(
        Order.user_id == user_id,
        Order.order_date >= start_date,
        Order.order_date <= end_date
    ).order_by(Order.order_date).all()
    
    dates = []
    amounts = []
    current_date = start_date
    
    while current_date <= end_date:
        dates.append(current_date.strftime('%Y-%m-%d'))
        day_orders = [o for o in orders if o.order_date.date() == current_date.date()]
        amounts.append(sum(order.total_amount for order in day_orders))
        current_date += timedelta(days=1)
    
    return jsonify({
        'dates': dates,
        'amounts': amounts
    })

@app.route('/api/users/<int:user_id>/shopping-preference')
def user_shopping_preference(user_id):
    # 获取用户的所有订单
    orders = Order.query.filter_by(user_id=user_id).all()
    
    # 从订单中提取所有订单项
    order_items = []
    for order in orders:
        items = OrderItem.query.filter_by(order_id=order.id).all()
        order_items.extend(items)
    
    # 按产品类别统计消费金额
    category_amounts = {}
    for item in order_items:
        product = Product.query.get(item.product_id)
        if product and product.category:
            amount = item.price * item.quantity
            category_amounts[product.category] = category_amounts.get(product.category, 0) + amount
    
    if not category_amounts:
        return jsonify({
            'labels': [],
            'values': [],
            'type': 'pie',
            'error': 'No shopping data available'
        })
    
    # 转换为Plotly所需的格式
    return jsonify({
        'labels': list(category_amounts.keys()),
        'values': list(category_amounts.values()),
        'type': 'pie'
    })

@app.route('/api/orders/<int:order_id>')
def get_order_details(order_id):
    order = Order.query.get_or_404(order_id)
    return jsonify({
        'order_number': order.order_number,
        'order_date': order.order_date.strftime('%Y-%m-%d %H:%M'),
        'payment_method': order.payment_method,
        'payment_time': order.payment_time.strftime('%Y-%m-%d %H:%M'),
        'status': order.status,
        'total_amount': order.total_amount,
        'items': [{
            'product': {
                'name': item.product.name,
                'category': item.product.category
            },
            'price': item.price,
            'quantity': item.quantity
        } for item in order.items]
    })

@app.route('/api/users/<int:user_id>', methods=['GET', 'PUT', 'DELETE'])
def user_api(user_id):
    user = User.query.get_or_404(user_id)
    
    if request.method == 'GET':
        return jsonify({
            'id': user.id,
            'username': user.username,
            'email': user.email,
            'phone': user.phone,
            'membership_level': user.membership_level,
            'status': user.status,
            'consumption_score': user.consumption_score
        })
    
    elif request.method == 'PUT':
        data = request.json
        user.username = data.get('username', user.username)
        user.email = data.get('email', user.email)
        user.phone = data.get('phone', user.phone)
        user.membership_level = data.get('membership_level', user.membership_level)
        user.status = data.get('status', user.status) == 'true'
        
        try:
            db.session.commit()
            return jsonify({'message': 'User updated successfully'})
        except Exception as e:
            db.session.rollback()
            return jsonify({'error': str(e)}), 400
    
    elif request.method == 'DELETE':
        try:
            # 删除用户的所有订单项和订单
            orders = Order.query.filter_by(user_id=user_id).all()
            for order in orders:
                # 删除订单的所有订单项
                OrderItem.query.filter_by(order_id=order.id).delete()
                # 删除订单
                db.session.delete(order)
            
            # 删除用户的所有标签关联
            UserTag.query.filter_by(user_id=user_id).delete()
            
            # 删除用户的所有活动记录（如果存在UserActivity表）
            try:
                UserActivity.query.filter_by(user_id=user_id).delete()
            except Exception:
                pass  # 如果UserActivity表不存在，忽略错误
            
            # 最后删除用户
            db.session.delete(user)
            db.session.commit()
            
            return jsonify({'message': '用户删除成功'})
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"删除用户时发生错误: {str(e)}")
            return jsonify({'error': f'删除用户失败: {str(e)}'}), 400

@app.route('/api/users', methods=['POST'])
def create_user():
    try:
        data = request.json
        
        # 检查ID是否已存在（如果提供了ID）
        if data.get('id'):
            if User.query.get(data['id']):
                return jsonify({'error': '用户ID已存在'}), 400
        
        # 生成用户编号（如果没有提供）
        if not data.get('user_code'):
            # 生成格式：U + 年月日 + 4位随机数
            random_num = ''.join(random.choices('0123456789', k=4))
            data['user_code'] = f"U{datetime.now().strftime('%Y%m%d')}{random_num}"
        
        # 检查用户编号是否已存在
        if User.query.filter_by(user_code=data['user_code']).first():
            return jsonify({'error': '用户编号已存在'}), 400
            
        # 创建用户对象
        user_data = {
            'user_code': data['user_code'],
            'username': data['username'],
            'email': data['email'],
            'phone': data.get('phone', ''),
            'membership_level': data['membership_level'],
            'status': True,
            'registration_date': datetime.now(),
            'consumption_score': 0.0
        }
        
        # 如果提供了ID，添加到用户数据中
        if data.get('id'):
            user_data['id'] = int(data['id'])
        
        user = User(**user_data)
        db.session.add(user)
        db.session.commit()
        
        return jsonify({
            'id': user.id,
            'user_code': user.user_code,
            'username': user.username,
            'email': user.email,
            'phone': user.phone,
            'membership_level': user.membership_level,
            'status': user.status
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 400

@app.route('/api/shopping-preferences')
def get_shopping_preferences():
    # 获取所有已完成的订单的商品类别统计
    category_stats = db.session.query(
        Product.category,
        func.count(OrderItem.id).label('count')
    ).join(OrderItem, Product.id == OrderItem.product_id)\
     .join(Order, OrderItem.order_id == Order.id)\
     .filter(Order.status == 'completed')\
     .group_by(Product.category)\
     .all()
    
    # 转换为饼图所需的形式
    labels = [stat[0] for stat in category_stats]
    data = [stat[1] for stat in category_stats]
    
    # 计算总数用于计算百分比
    total = sum(data)
    percentages = [round((count / total) * 100, 1) for count in data]
    
    return jsonify({
        'labels': labels,
        'data': data,
        'percentages': percentages
    })

@app.route('/api/consumption-score-distribution')
def get_consumption_score_distribution():
    tag_name = request.args.get('tag_name', None)
    
    # 获取用户数据
    if tag_name:
        users = User.query.join(UserTag).join(Tag).filter(Tag.name == tag_name).all()
    else:
        users = User.query.all()
    
    # 获取消费得分
    scores = [user.consumption_score for user in users if user.consumption_score is not None]
    
    if not scores:
        return jsonify({
            'labels': [],
            'data': []
        })
    
    # 计算分布区间
    min_score = min(scores)
    max_score = max(scores)
    
    # 创建10个区间
    bins = np.linspace(min_score, max_score, 11)
    labels = [f"{int(bins[i])}-{int(bins[i+1])}" for i in range(len(bins)-1)]
    
    # 统计每个区间的用户数量
    hist, _ = np.histogram(scores, bins=bins)
    data = hist.tolist()
    
    return jsonify({
        'labels': labels,
        'data': data
    })

@app.route('/api/membership-distribution')
def get_membership_distribution():
    # 获取所有会员等级的用户数量
    membership_counts = db.session.query(
        User.membership_level,
        func.count(User.id)
    ).group_by(User.membership_level).all()
    
    # 转换为字典格式
    distribution = {
        'labels': [level for level, _ in membership_counts],
        'data': [count for _, count in membership_counts]
    }
    
    return jsonify(distribution)

def update_user_preference_tags(user_id):
    """根据用户的消费记录更新购物偏好标签"""
    # 获取用户的所有订单项
    user_orders = Order.query.filter_by(user_id=user_id).all()
    order_items = []
    for order in user_orders:
        order_items.extend(order.items)
    
    if not order_items:
        return
    
    # 统计各个类别的消费金额
    category_amounts = {}
    for item in order_items:
        category = item.product.category
        amount = item.price * item.quantity
        category_amounts[category] = category_amounts.get(category, 0) + amount
    
    # 计算总消费金额
    total_amount = sum(category_amounts.values())
    
    # 计算各类别的消费占比
    category_percentages = {
        category: (amount / total_amount) * 100 
        for category, amount in category_amounts.items()
    }
    
    # 获取用户当前的标签
    user = User.query.get(user_id)
    current_tags = set(tag.name for tag in user.tags)
    
    # 根据消费占比生成新的标签
    new_tags = set()
    for category, percentage in category_percentages.items():
        if percentage >= 50:  # 如果某个类别占比超过50%
            tag_name = f"偏好{category}"
            new_tags.add(tag_name)
        elif percentage >= 30:  # 如果某个类别占比超过30%
            tag_name = f"喜欢{category}"
            new_tags.add(tag_name)
    
    # 添加消费水平标签
    if total_amount > 10000:
        new_tags.add("高消费")
    elif total_amount > 5000:
        new_tags.add("中等消费")
    else:
        new_tags.add("低消费")
    
    # 更新用户标签
    # 删除旧的偏好和消费水平标签
    user.tags = [tag for tag in user.tags 
                if not (tag.name.startswith(("偏好", "喜欢")) or 
                       tag.name in ["高消费", "中等消费", "低消费"])]
    
    # 添加新标签
    for tag_name in new_tags:
        # 检查标签是否存在，不存在则创建
        tag = Tag.query.filter_by(name=tag_name).first()
        if not tag:
            tag = Tag(
                name=tag_name,
                category="购物偏好",
                description=f"根据用户消费记录自动生成的{tag_name}标签"
            )
            db.session.add(tag)
        
        if tag not in user.tags:
            user.tags.append(tag)
    
    db.session.commit()

def predict_user_consumption(user_id):
    """预测用户未来消费趋势"""
    # 获取用户的所有订单
    orders = Order.query.filter_by(user_id=user_id).order_by(Order.order_date).all()
    
    if len(orders) < 2:  # 需要至少两个点来进行预测
        return None
    
    # 准备数据
    dates = [order.order_date for order in orders]
    amounts = [order.total_amount for order in orders]
    
    # 转换日期为数值（距离第一个订单的天数）
    first_date = min(dates)
    X = np.array([(date - first_date).days for date in dates]).reshape(-1, 1)
    y = np.array(amounts)
    
    # 创建并训练模型
    model = LinearRegression()
    model.fit(X, y)
    
    # 预测未来30天的消费
    future_days = np.array(range(max(X.flatten()) + 1, max(X.flatten()) + 31)).reshape(-1, 1)
    future_amounts = model.predict(future_days)
    
    # 计算预测区间
    confidence = 0.95
    n = len(X)
    mean_x = np.mean(X)
    
    # 计算预测区间
    se = np.sqrt(np.sum((y - model.predict(X)) ** 2) / (n - 2))
    x_new = future_days.reshape(-1, 1)
    
    # 计算预测区间
    pi = stats.t.ppf(1 - (1 - confidence) / 2, n - 2) * se * np.sqrt(1 + 1/n + (x_new - mean_x) ** 2 / np.sum((X - mean_x) ** 2))
    
    # 创建可视化
    fig = make_subplots(rows=2, cols=1, 
                       subplot_titles=('历史消费和预测趋势', '消费类别分布'),
                       vertical_spacing=0.15)
    
    # 添加历史数据点
    fig.add_trace(
        go.Scatter(
            x=[first_date + timedelta(days=int(x[0])) for x in X],
            y=y,
            mode='markers',
            name='历史消费',
            marker=dict(color='blue')
        ),
        row=1, col=1
    )
    
    # 添加预测线
    fig.add_trace(
        go.Scatter(
            x=[first_date + timedelta(days=int(x)) for x in future_days],
            y=future_amounts,
            mode='lines',
            name='预测趋势',
            line=dict(color='red', dash='dash')
        ),
        row=1, col=1
    )
    
    # 添加预测区间
    fig.add_trace(
        go.Scatter(
            x=[first_date + timedelta(days=int(x)) for x in future_days] + 
              [first_date + timedelta(days=int(x)) for x in future_days[::-1]],
            y=list(future_amounts + pi) + list(future_amounts - pi)[::-1],
            fill='toself',
            fillcolor='rgba(255,0,0,0.1)',
            line=dict(color='rgba(255,0,0,0)'),
            name='95% 预测区间'
        ),
        row=1, col=1
    )
    
    # 添加消费类别分布饼图
    category_amounts = {}
    for order in orders:
        for item in order.items:
            category = item.product.category
            amount = item.price * item.quantity
            category_amounts[category] = category_amounts.get(category, 0) + amount
    
    fig.add_trace(
        go.Pie(
            labels=list(category_amounts.keys()),
            values=list(category_amounts.values()),
            name='消费类别分布'
        ),
        row=2, col=1
    )
    
    # 更新布局
    fig.update_layout(
        height=800,
        showlegend=True,
        title_text="用户消费分析与预测",
        title_x=0.5
    )
    
    # 更新x轴标签
    fig.update_xaxes(title_text="日期", row=1, col=1)
    fig.update_yaxes(title_text="消费金额 (元)", row=1, col=1)
    
    return fig.to_json()

@app.route('/api/user/<int:user_id>/consumption-prediction')
def get_consumption_prediction(user_id):
    try:
        prediction_data = predict_user_consumption(user_id)
        if prediction_data is None:
            return jsonify({'error': '数据不足，无法进行预测'}), 400
        return jsonify({'data': prediction_data})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/upload-users', methods=['POST'])
def upload_users():
    """处理用户批量导入"""
    # 检查是否有文件上传
    if 'file' not in request.files:
        return jsonify({'error': '没有文件被上传'}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': '没有选择文件'}), 400
    
    if not allowed_file(file.filename):
        return jsonify({'error': '不支持的文件类型'}), 400
    
    # 保存文件
    filename = secure_filename(file.filename)
    filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
    file.save(filepath)
    
    try:
        # 读取CSV文件
        df = pd.read_csv(filepath, encoding='utf-8')
        
        # 处理每一行数据
        success_count = 0
        error_count = 0
        errors = []
        
        # 获取新用户标签
        new_user_tag = Tag.query.filter_by(name='新注册用户').first()
        
        for index, row in df.iterrows():
            try:
                # 检查必填字段
                if pd.isna(row['username']) or pd.isna(row['email']):
                    raise ValueError(f"用户名和邮箱为必填项")
                
                # 检查用户是否已存在
                if User.query.filter_by(email=row['email']).first():
                    error_count += 1
                    errors.append(f"第 {index + 1} 行：邮箱已存在")
                    continue
                
                # 创建新用户
                user_data = {
                    'username': row['username'],
                    'email': row['email'],
                    'phone': row.get('phone', ''),
                    'membership_level': row.get('membership_level', '普通会员'),
                    'registration_date': datetime.now(),
                    'consumption_score': float(row.get('consumption_score', 0)),
                    'status': bool(int(row.get('status', 1)))
                }
                
                # 如果提供了ID，检查并添加
                if 'id' in row and not pd.isna(row['id']):
                    if User.query.get(int(row['id'])):
                        error_count += 1
                        errors.append(f"第 {index + 1} 行：用户ID已存在")
                        continue
                    user_data['id'] = int(row['id'])
                
                # 如果提供了用户编号，检查并添加
                if 'user_code' in row and not pd.isna(row['user_code']):
                    if User.query.filter_by(user_code=row['user_code']).first():
                        error_count += 1
                        errors.append(f"第 {index + 1} 行：用户编号已存在")
                        continue
                    user_data['user_code'] = row['user_code']
                else:
                    # 生成用户编号
                    random_num = ''.join(random.choices('0123456789', k=4))
                    user_data['user_code'] = f"U{datetime.now().strftime('%Y%m%d')}{random_num}"
                
                user = User(**user_data)
                db.session.add(user)
                db.session.flush()  # 获取用户ID
                
                # 添加新用户标签
                if new_user_tag:
                    user_tag = UserTag(
                        user_id=user.id,
                        tag_id=new_user_tag.id,
                        last_updated=datetime.now()
                    )
                    db.session.add(user_tag)
                
                success_count += 1
                
            except Exception as e:
                error_count += 1
                errors.append(f"第 {index + 1} 行：{str(e)}")
                continue
        
        # 提交事务
        db.session.commit()
        
        # 删除临时文件
        if os.path.exists(filepath):
            os.remove(filepath)
        
        return jsonify({
            'success': True,
            'message': f'成功导入 {success_count} 条记录，失败 {error_count} 条',
            'errors': errors
        })
        
    except Exception as e:
        # 发生错误时回滚事务
        db.session.rollback()
        
        # 删除临时文件
        if os.path.exists(filepath):
            os.remove(filepath)
        
        return jsonify({
            'success': False,
            'error': f'文件处理失败：{str(e)}'
        }), 400

def get_order_status_color(status):
    colors = {
        'completed': 'success',
        'pending': 'warning',
        'cancelled': 'danger'
    }
    return colors.get(status.lower(), 'secondary')

# 添加函数到模板上下文
app.jinja_env.globals.update(get_order_status_color=get_order_status_color)

def init_tags():
    """初始化标签数据"""
    try:
        # 检查是否已经有标签数据
        if Tag.query.count() > 0:
            print("标签已存在，跳过初始化")
            return

        # 用户价值标签
        value_tags = [
            {'name': '高价值客户', 'category': '用户价值'},
            {'name': 'VIP会员', 'category': '用户价值'}
        ]

        # 活跃度标签
        activity_tags = [
            {'name': '活跃客户', 'category': '活跃度'},
            {'name': '潜在客户', 'category': '活跃度'},
            {'name': '流失客户', 'category': '活跃度'}
        ]

        # 客户类型标签
        type_tags = [
            {'name': '新注册用户', 'category': '客户类型'},
            {'name': '常住客户', 'category': '客户类型'},
            {'name': '商务客户', 'category': '客户类型'},
            {'name': '休闲客户', 'category': '客户类型'}
        ]

        # 创建所有标签
        all_tags = value_tags + activity_tags + type_tags
        for tag_data in all_tags:
            tag = Tag(
                name=tag_data['name'],
                category=tag_data['category']
            )
            db.session.add(tag)

        db.session.commit()
        print("标签初始化完成")
        
    except Exception as e:
        print(f"初始化标签时出错: {str(e)}")
        db.session.rollback()

# 创建数据库表并初始化标签
with app.app_context():
    # db.create_all()
    init_tags()

@app.route('/api/tags/<int:tag_id>/users')
def get_tag_users(tag_id):
    # 获取具有该标签的所有用户
    users = User.query.join(UserTag).filter(UserTag.tag_id == tag_id).all()
    
    return jsonify([{
        'id': user.id,
        'username': user.username,
        'email': user.email,
        'membership_level': user.membership_level
    } for user in users])

@app.route('/profile/settings', methods=['GET', 'POST'])
@login_required
def profile_settings():
    if request.method == 'POST':
        current_password = request.form.get('current_password')
        user = Admin.query.get(session['user_id'])
        
        if not user.check_password(current_password):
            return render_template('profile_settings.html', 
                                 error_message='当前密码错误',
                                 current_user=user)
        
        # 更新用户信息
        try:
            user.username = request.form.get('username')
            user.email = request.form.get('email')
            
            # 如果提供了新密码，则更新密码
            new_password = request.form.get('new_password')
            if new_password:
                user.set_password(new_password)
            
            db.session.commit()
            return render_template('profile_settings.html', 
                                 success_message='个人信息更新成功',
                                 current_user=user)
        except Exception as e:
            db.session.rollback()
            return render_template('profile_settings.html', 
                                 error_message='更新失败，请稍后重试',
                                 current_user=user)
    
    user = Admin.query.get(session['user_id'])
    return render_template('profile_settings.html', current_user=user)

# ...existing code...

@app.route('/user-search', methods=['GET', 'POST'])
@login_required
def user_search():
    page = request.args.get('page', 1, type=int)
    username = request.form.get('username', '')
    email = request.form.get('email', '')
    phone = request.form.get('phone', '')
    selected_tag = request.form.get('tags', '')

    # 查询具有选定标签的用户
    query = User.query.filter(
        User.username.ilike(f'%{username}%'),
        User.email.ilike(f'%{email}%'),
        User.phone.ilike(f'%{phone}%')
    )
    if selected_tag:
        query = query.join(User.tags).filter(Tag.name == selected_tag).distinct()

    users = query.paginate(page=page, per_page=10)

     # 计算用户总数、活跃人数和平均消费分数
    total_users = query.count()
    active_users = query.filter(User.status == 1).count()
    average_spending_score = query.with_entities(func.avg(User.consumption_score)).scalar()
    average_spending_score = round(average_spending_score, 2) if average_spending_score else 0.00
    
    # 获取标签数据
    tag_data = db.session.query(Tag.name, func.count(UserTag.user_id)) \
                .join(UserTag).group_by(UserTag.tag_id).all()

    return render_template('user_search.html', users=users, username=username, email=email, phone=phone, selected_tag=selected_tag, tags=Tag.query.all(), total_users=total_users, active_users=active_users, average_spending_score=average_spending_score, tag_data=tag_data)

    
# ...existing code...
if __name__ == '__main__':
    app.run(debug=True, port=7000)
